Estudio de ubicaciones con más accidentes

Con el modelo descriptivo obtenido a través del Power BI, se puede observar que el barrio con más accidentes es la Dreta de l’Eixample. Ahora se quiere buscar si dentro de este barrio hay unas ubicaciones donde los accidentes son más frecuentes. Por eso, se diseñan todas las ubicaciones de los accidentes en un mapa y se sacan visualmente los puntos con más accidentes.

1. Conexión de la BBDD a R

#install.packages("RPostgreSQL")
require("RPostgreSQL")
## Loading required package: RPostgreSQL
## Loading required package: DBI
# Guardar el Password para poder posteriormente eliminarlo
pw <- {"ludo"}

# Leer el driver de PostgreSQL
drv <- dbDriver("PostgreSQL")

# Crear la conexion con la base de datos
con <- dbConnect(drv, dbname = "accidentes_barcelona",
                 host = "192.168.0.165", port = 5432,
                 user = "postgres", password = pw)

# Eliminar el Password
rm(pw) 

# Chequear que no existe la tabla de la base de datos
dbExistsTable(con, "descripcion_marca_vehiculo")
## [1] TRUE

2. Extracción de los datos necesarios por el estudio

Se busca el código del barrio la Dreta de l’Eixample.

barrios <- dbGetQuery(con, "SELECT * FROM descripcion_barrio_accidente")
barrios
##    id_desc_barrio                                  desc_barrio
## 1              64                   el Camp de l'Arpa del Clot
## 2              65                                      el Clot
## 3              72                     Sant Martí de Provençals
## 4              73                          la Verneda i la Pau
## 5              70                        el Besòs i el Maresme
## 6              71                      Provençals del Poblenou
## 7              68                                  el Poblenou
## 8              69 Diagonal Mar i el Front Marítim del Poblenou
## 9              66            el Parc i la Llacuna del Poblenou
## 10             67                la Vila Olímpica del Poblenou
## 11              1                                     el Raval
## 12              2                               el Barri Gòtic
## 13              3                               la Barceloneta
## 14              4        Sant Pere, Santa Caterina i la Ribera
## 15             10                                  Sant Antoni
## 16              9               la Nova Esquerra de l'Eixample
## 17              8              l'Antiga Esquerra de l'Eixample
## 18              7                       la Dreta de l'Eixample
## 19              6                           la Sagrada Família
## 20              5                                el Fort Pienc
## 21             11                                 el Poble Sec
## 22             12                   la Marina del Prat Vermell
## 23             13                            la Marina de Port
## 24             14                        la Font de la Guatlla
## 25             15                                  Hostafrancs
## 26             16                                   la Bordeta
## 27             17                                Sants - Badal
## 28             18                                        Sants
## 29             19                                    les Corts
## 30             20                   la Maternitat i Sant Ramon
## 31             21                                    Pedralbes
## 32             26                       Sant Gervasi - Galvany
## 33             23                                       Sarrià
## 34             24                              les Tres Torres
## 35             25                   Sant Gervasi - la Bonanova
## 36             27                         el Putxet i el Farró
## 37             22        Vallvidrera, el Tibidabo i les Planes
## 38             28                    Vallcarca i els Penitents
## 39             30                                     la Salut
## 40             29                                      el Coll
## 41             31                            la Vila de Gràcia
## 42             32           el Camp d'en Grassot i Gràcia Nova
## 43             38                                 la Teixonera
## 44             39                     Sant Genís dels Agudells
## 45             41                             la Vall d'Hebron
## 46             40                                      Montbau
## 47             36                         la Font d'en Fargues
## 48             42                                     la Clota
## 49             43                                        Horta
## 50             34                                     Can Baró
## 51             37                                    el Carmel
## 52             33                             el Baix Guinardó
## 53             35                                  el Guinardó
## 54             54                                   Torre Baró
## 55             49                                    Canyelles
## 56             50                                 les Roquetes
## 57             48                                 la Guineueta
## 58             53                             la Trinitat Nova
## 59             51                                       Verdun
## 60             52                               la Prosperitat
## 61             44                Vilapicina i la Torre Llobeta
## 62             45                                        Porta
## 63             46                          el Turó de la Peira
## 64             61                                   la Sagrera
## 65             62                     el Congrés i els Indians
## 66             63                                        Navas
## 67             60                                  Sant Andreu
## 68             57                            la Trinitat Vella
## 69             58                                Baró de Viver
## 70             59                                el Bon Pastor
## 71             -1                                   Desconegut
## 72             56                                     Vallbona
## 73             47                                  Can Peguera
## 74             55                             Ciutat Meridiana

Este barrio tiene código 7. Entonces se buscan latitud y longitud de las ubicaciones de los accidentes en este barrio.

datos <- dbGetQuery(con, "SELECT latitud, longitud FROM accidente WHERE id_desc_barrio = 7 AND latitud != -1.0 AND longitud != -1.0")
head(datos)
##    latitud longitud
## 1 41.39170 2.174108
## 2 41.39463 2.165751
## 3 41.39910 2.166929
## 4 41.39988 2.173511
## 5 41.39790 2.172665
## 6 41.39372 2.160864

3. Diseño del mapa de accidentes

Se instalan unas librerias necesarias para diseñar los puntos en un mapa.

#install.packages("devtools")
library(devtools)
#install_github("rOpenSpain/caRtociudad")
library(caRtociudad)
#install.packages("ggmap")
library(ggmap)
## Loading required package: ggplot2
## Google's Terms of Service: https://cloud.google.com/maps-platform/terms/.
## Please cite ggmap if you use it! See citation("ggmap") for details.

Se elige cómo centro del mapa un punto más o menos central del barrio, para visualizar el mapa de manera mejor. Entonces se diseña el mapa de los accidentes.

bcn <- cartociudad_geocode("calle del bruc 80, 08009 barcelona")
mapa_bcn <- cartociudad_get_map(c(bcn$lat, bcn$lng), 2.7)
mapa <- ggmap::ggmap(mapa_bcn)
mapa + geom_point(aes(x = longitud, y = latitud), data = datos, size = 1)
## Warning: Removed 3 rows containing missing values (geom_point).

Se nota que hay un deslizamiento constante en los puntos. En los CSVs de origen de los años 2016 y 2017 se guardaba la información de latitud y longitud, entonces es suficiente mirar estos valores y compararlos con los valores obtenidos durante la transformación de UTM a latitud y longitud. Se encuentra así el valor que se tiene que restar.

datos$longitud <- datos$longitud - 0.001103145
datos$latitud <- datos$latitud - 0.00184649

Se vuelve a diseñar el mapa para verificar que se haya solucionado el problema de deslizamiento.

mapa + geom_point(aes(x = longitud, y = latitud), data = datos, size = 1)

Se puede notar que los accidentes se distribuyen de manera más o menos uniforme en todas las calles de este barrio. En particular, parecen ligeramente más frecuentes los accidentes al cruce entre Carrer de Pau Claris y Calle Provenza, al cruce entre Avenida Diagonal y Paseo de San Juan y en Plaza de Tetuán.

4. Estudio de accidentes mortales

Parece interesante saber también la distribución de los accidentes mortales. Entonces se repite el proceso utilizando sólo las coordenadas de los accidentes mortales.

Se busca el código del la condición fisica de la victima Muerte.

condicion_fisica <- dbGetQuery(con, "SELECT * FROM descripcion_condicion_fisica_victima")
condicion_fisica
##   id_desc_condicion_fisica
## 1                        1
## 2                        2
## 3                        3
## 4                        4
## 5                        5
## 6                        6
## 7                        7
## 8                        8
##                                         desc_condicion_fisica
## 1                                                 Herido leve
## 2                                                Herido grave
## 3                                                      Muerte
## 4                      Herido leve: Hospitalización hasta 24h
## 5                Herido grave: Hospitalización superior a 24h
## 6 Herido leve: Con asistencia sanitaria en lugar de accidente
## 7                   Herido leve: Rechaza asistencia sanitaria
## 8                   Muerte (dentro 24h posteriores accidente)

Los muertos tienen código 3 y 8. Entonces se buscan las ubicaciones de los accidentes con muertos.

datos <- dbGetQuery(con, "SELECT accidente.num_expediente, latitud, longitud FROM accidente, victima_involucrada WHERE accidente.num_expediente = victima_involucrada.num_expediente AND id_desc_condicion_fisica IN (3, 8) AND latitud != -1.0 AND longitud != -1.0 AND id_desc_barrio = 7")
datos
##   num_expediente  latitud longitud
## 1    2015S003699 41.39886 2.161633
## 2    2016S002167 41.39840 2.162981
## 3    2016S006296 41.39404 2.170721

Se diseñan estos accidentes en el mapa.

datos$longitud <- datos$longitud - 0.001103145
datos$latitud <- datos$latitud - 0.00184649
mapa + geom_point(aes(x = longitud, y = latitud), data = datos, size = 3)

Se puede notar que dos accidentes mortales sobre tres tienen como ubicación Avenida Diagonal, a la altura de la Casa Comalat. Se sospecha por eso que allí pueda existir un problema de circulación.